package com.youme.tools.cg.db;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.jsp.jstl.sql.Result;

import com.youme.tools.cg.db.enums.MySqlDataType;
import com.youme.tools.cg.db.util.ConnHelper;
import com.youme.tools.cg.db.util.JdbcHelper;
import com.youme.tools.cg.pojo.FieldBean;
import com.youme.tools.cg.pojo.TableBean;

public class MySqlReader implements DbReader {
  
  /** 主键 Key Name */
  static final String PK_KEY_NAME = "PRI";

  @SuppressWarnings("unused")
  @Override
  public List<TableBean> readTable(List<String> tableNames) throws SQLException {
    List<TableBean> list = new ArrayList<>();
    Result result = null;
    Connection conn = null;
    conn = ConnHelper.getJDBCConn();

    String sql = "SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.TABLES"
        + " WHERE table_schema = ? AND table_type = 'base table' ";
    if (null != tableNames && !tableNames.isEmpty()) {
      StringBuilder tableNameInSql = new StringBuilder(" AND TABLE_NAME IN (");
      for (String name : tableNames) {
        tableNameInSql.append(" ?,");
      }
      tableNameInSql.setCharAt(tableNameInSql.length() - 1, ' ');
      tableNameInSql.append(")");
      sql += tableNameInSql.toString();
    }

    List<String> params = new ArrayList<String>();
    params.add(DB_NAME);
    if (null != tableNames && !tableNames.isEmpty()) {
      for (String name : tableNames) {
        params.add(name);
      }
    }

    result = JdbcHelper.executeQuery(conn, sql, params, false);
    if (null != result && 0 < result.getRowCount()) {
      for (int i = 0; i < result.getRowCount(); i++) {
        Map<?, ?> map = result.getRows()[i];
        list.add(loadTableInfo(map));
      }
    }
    JdbcHelper.closeAll(conn, null, null, null);
    if (null != list && !list.isEmpty()) {
      for (TableBean table : list) {
        conn = ConnHelper.getJDBCConn();
        List<FieldBean> fields = new ArrayList<>();
        List<FieldBean> pks = new ArrayList<>();
        sql = "SELECT COLUMN_NAME, DATA_TYPE, COLUMN_KEY, COLUMN_COMMENT"
            + " FROM information_schema.COLUMNS WHERE table_schema = ? AND table_name = ?";
        params.clear();
        params.add(DB_NAME);
        params.add(table.getOriginalName());
        result = JdbcHelper.executeQuery(conn, sql, params, false);
        if (null != result && 0 < result.getRowCount()) {
          for (int i = 0; i < result.getRowCount(); i++) {
            Map<?, ?> map = result.getRows()[i];
            FieldBean field = loadFieldInfo(map);
            fields.add(field);
            if (field.getIsPK()) {
              pks.add(field);
            }
          }
        }
        JdbcHelper.closeAll(conn, null, null, null);
        table.setFields(fields);
        table.setPks(pks);
      }
    }

    return list;
  }

  @Override
  public String convertDataType(Object obj) {
    String dbType = obj.toString();
    return MySqlDataType.getJavaTypeByDbType(dbType);
  }

  @Override
  public boolean isPK(Object obj) {
    if (null != obj) {
      String keyName = obj.toString();
      if (PK_KEY_NAME.equals(keyName)) {
        return true;
      }
    }
    return false;
  }

  /**
   * 读取表信息
   * 
   * @param map
   * @return 表信息
   */
  private TableBean loadTableInfo(Map<?, ?> map) {
    TableBean item = new TableBean();
    String tableName = map.get("TABLE_NAME").toString();
    item.setOriginalName(tableName);
    if (tableName.charAt(1) == '_') {
      tableName = tableName.substring(2);
    }
    item.setName(tableName);
    if (null != map.get("TABLE_COMMENT")) {
      item.setDescription(map.get("TABLE_COMMENT").toString());
    } else {
      item.setDescription("");
    }
    return item;
  }

  /**
   * 读取字段信息
   * 
   * @param map
   * @return 字段信息
   */
  private FieldBean loadFieldInfo(Map<?, ?> map) {
    FieldBean item = new FieldBean();
    item.setName(map.get("COLUMN_NAME").toString());
    item.setDataType(convertDataType(map.get("DATA_TYPE")));
    item.setIsPK(isPK(map.get("COLUMN_KEY")));
    if (null != map.get("COLUMN_COMMENT")) {
      item.setDescription(map.get("COLUMN_COMMENT").toString());
    } else {
      item.setDescription("");
    }
    return item;
  }
}
